create or replace package ac_diff_not_in is

  -- Author  : LEV ERUSALIMSKIY
  -- Created : 13/01/2012 8:43:53 PM

 procedure app_appl_authorization_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_page_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number);

 procedure appl_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_appl_lov_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_report_cols_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number);

 procedure app_page_branches_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_regions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_validations_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_auth_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_pages_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort in out number);

 procedure app_comp_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_proc_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number);

 procedure app_page_buttons_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_dyn_actions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_report_ir_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);

 procedure app_report_ir_col_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number);
end ac_diff_not_in;
/
create or replace package body ac_diff_not_in is

 procedure app_page_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_page_id number) is
   v_parent_id number;
   v_msg      varchar2(1000);
   begin

   v_msg       := ac_utils.in1NotIn2('Items', pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_PAGE_ITEMS );

   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, pi_parent_id, t.item_name, 'N', 'N' from
   (
    select app1.item_name from apex_application_page_items app1
    where app1.application_id = pi_app_id1
      and app1.page_id = p_page_id
     minus
    select app1.item_name from apex_application_page_items app1
    where app1.application_id = pi_app_id2
      and app1.page_id = p_page_id
    ) t;
 end app_page_items_not_in;

 procedure appl_items_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id      number;
 v_title          varchar2(1000) := 'Appl Items';
 v_msg            varchar2(1000);
 begin

   v_msg         := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id   := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_APPLICATION_ITEM );
   insert into ac_results
   (result_id,     parent_id, param_name, is_branch, is_src)
   select
    p_result_id, v_parent_id, t.item_name, 'N', 'N' from
   ( 
       select ai.item_name 
       from  apex_application_items ai
       where ai.application_id = pi_app_id1
       minus
       select ai.item_name 
       from  apex_application_items ai
       where ai.application_id = pi_app_id2
   ) t;
     
 end  appl_items_not_in;

 procedure app_appl_lov_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id_1    number;
 v_msg            varchar2(1000);
 v_title          varchar2(1000) := 'LOVS';
 begin
    v_msg         := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_LOVS );
    insert into ac_results
    (result_id,   parent_id,     param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id_1, t.list_of_values_name, 'N', 'N'
    from
    (
     select t.list_of_values_name from APEX_APPLICATION_LOVS t
       where t.application_id = pi_app_id1
      minus
      select t.list_of_values_name from APEX_APPLICATION_LOVS t
       where t.application_id = pi_app_id2
    ) t;
     
 end  app_appl_lov_not_in;

 procedure app_pages_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort in out number) is
   v_parent_id number;
   v_msg       varchar2(1000);
   begin
     v_msg := ac_utils.in1NotIn2('Pages', pi_app_id1, pi_app_id2);

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, p_sort, true, ac_utils.G_PAGE);
     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.page_id, 'N', 'N' from
      ( select app.page_id  
        from apex_application_pages app 
        where app.application_id = pi_app_id1
         minus  
        select app.page_id  
        from apex_application_pages app 
        where app.application_id = pi_app_id2
      ) t;
 end app_pages_not_in;

 procedure app_report_cols_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number, pi_region_id1 number, pi_region_id2 number) is
   v_parent_id      number;
   v_parent_id_cols number;
   v_column_alias   varchar2(100) := '**==**';
   v_msg            varchar2(1000);
   v_title          varchar2(100) := 'Report columns';
   begin

     v_msg := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);

     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1, true, ac_utils.G_REPORT_COLUMN);

     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.COLUMN_ALIAS, 'N', 'N' from
      ( select rc1.COLUMN_ALIAS
        from apex_application_page_rpt_cols rc1
        where rc1.application_id = pi_app_id1
          and rc1.page_id = pi_page_id
          and rc1.region_id = pi_region_id1  
         minus  
        select rc2.COLUMN_ALIAS
        from apex_application_page_rpt_cols rc2
        where rc2.application_id = pi_app_id2
          and rc2.page_id = pi_page_id
          and rc2.region_id = pi_region_id2      
       ) t;
 end app_report_cols_not_in;

 procedure app_page_branches_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
  v_parent_id   number; 
  v_parent_id_1 number; 
  v_branch_sig  varchar2(100) := '******';
  v_msg         varchar2(1000);
  v_title       varchar2(1000) := 'Branches';
 begin
    v_msg := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);

    v_parent_id_1 := ac_utils.ins_result_msg(p_result_id,  pi_parent_id,  v_msg,   1, true, ac_utils.G_BRANCH);

     insert into ac_results
     (result_id, parent_id, param_name, is_branch, is_src)
     select p_result_id, v_parent_id_1, t.BRANCH_POINT||':'||t.process_sequence, 'N', 'N'  from
      ( select t.BRANCH_POINT, t.process_sequence 
        from APEX_APPLICATION_PAGE_BRANCHES t
          where t.application_id = pi_app_id1
            and t.page_id = pi_page_id 
          minus  
        select t.BRANCH_POINT, t.process_sequence 
        from APEX_APPLICATION_PAGE_BRANCHES t
        where t.application_id = pi_app_id2
            and t.page_id = pi_page_id
       ) t;
     
 end  app_page_branches_not_in; 

 procedure app_regions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is 
 v_parent_id number;
 v_title     varchar2(100) := 'Regions';
 v_msg       varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1);
   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, pi_parent_id, t.region_name, 'N', 'N' from
   (select reg1.region_name from apex_application_page_regions reg1
    where reg1.application_id = pi_app_id1
      and reg1.page_id = pi_page_id
     minus
    select reg1.region_name from apex_application_page_regions reg1
    where reg1.application_id = pi_app_id2
      and reg1.page_id = pi_page_id
    ) t;
     
 end  app_regions_not_in;

 procedure app_page_validations_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_title          varchar2(1000) := ac_utils.G_VALIDATIONS;
 v_msg            varchar2(1000);
 v_parent_id      number;
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg,   1, true, ac_utils.G_VALIDATIONS );
    insert into ac_results
    (result_id,   parent_id,     param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id, t.validation_name, 'N', 'N'
    from
    (
     select t.validation_name from APEX_APPLICATION_PAGE_VAL t
       where t.application_id = pi_app_id1
         and t.page_id = pi_page_id
      minus
      select t.validation_name from APEX_APPLICATION_PAGE_VAL t
       where t.application_id = pi_app_id2
         and t.page_id = pi_page_id
    ) t;
    
 end app_page_validations_not_in;
  
 procedure app_auth_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_title          varchar2(1000) := ac_utils.G_AUTH_SCHEMES;
 v_msg            varchar2(1000);
 v_parent_id      number;
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg,   1, true, ac_utils.G_AUTH_SCHEMES );
    insert into ac_results
    (result_id,   parent_id,   param_name, is_branch, is_src)
    select
     p_result_id, v_parent_id, t.authentication_scheme_name, 'N', 'N'
    from
    (
     select t.authentication_scheme_name from APEX_APPLICATION_AUTH t
       where t.application_id = pi_app_id1
      minus
      select t.authentication_scheme_name from APEX_APPLICATION_AUTH t
       where t.application_id = pi_app_id2
    ) t;
 end  app_auth_not_in;


 procedure app_comp_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is  
 v_parent_id number;
 v_title     varchar2(100) := ac_utils.in1NotIn2('Computations', pi_app_id1, pi_app_id2);
 begin
     v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_title, 1);
     insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.COMPUTATION_ITEM, 'N', 'N' from
     (select c.COMPUTATION_ITEM
      from APEX_APPLICATION_COMPUTATIONS c
      where c.application_id = pi_app_id1
       minus
      select c.COMPUTATION_ITEM
      from APEX_APPLICATION_COMPUTATIONS c
      where c.application_id = pi_app_id2
     ) t;
     
 end  app_comp_not_in;

 procedure app_proc_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
  v_parent_id number;
  v_msg       varchar2(1000);
  v_title     varchar2(1000) := 'Processes';
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_PROCESS);
   
   insert into ac_results
   (result_id,   parent_id,    param_name, is_branch, is_src)
   select p_result_id, v_parent_id, t.PROCESS_NAME, 'N', 'N' from
   (select c.PROCESS_NAME
    from APEX_APPLICATION_Processes c
    where c.application_id = pi_app_id1
     minus
    select c.PROCESS_NAME
    from APEX_APPLICATION_Processes c
    where c.application_id = pi_app_id2
   ) t;
   
 end  app_proc_not_in; 

 procedure app_page_buttons_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number; 
 v_title       varchar2(10)  := 'Buttons';
 v_msg         varchar2(1000);
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_BUTTON );
    insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.BUTTON_NAME,'N', 'N' from
     (select b.BUTTON_NAME 
      from apex_application_page_buttons b 
      where b.application_id = pi_app_id1
        and b.page_id =  pi_page_id
       minus
      select  b.BUTTON_NAME 
      from apex_application_page_buttons b 
      where b.application_id = pi_app_id2
        and b.page_id =  pi_page_id     
      ) t;
 end app_page_buttons_not_in;

 procedure app_page_dyn_actions_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number; 
 v_title       varchar2(20)  := ac_utils.G_DYN_ACTIONS;
 v_msg         varchar2(1000);
 begin
    v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
    v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, v_msg, 1 , true, ac_utils.G_BUTTON );
    insert into ac_results
     (result_id,   parent_id,    param_name, is_branch, is_src)
     select p_result_id, v_parent_id, t.dynamic_action_name ,'N', 'N' from
     (select da.dynamic_action_name 
      from apex_application_page_da da 
      where da.application_id = pi_app_id1
        and da.page_id =  pi_page_id
       minus
      select  da.dynamic_action_name 
      from apex_application_page_da da
      where da.application_id = pi_app_id2
        and da.page_id =  pi_page_id     
      ) t;
 end app_page_dyn_actions_not_in;

 procedure app_appl_authorization_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number) is
 v_parent_id   number; 
 v_title       varchar2(60)  := ac_utils.G_AUTHORIZATION ;
 v_msg         varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_AUTHORIZATION );
    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.authorization_scheme_name ,'N', 'N' from
    (select auth.authorization_scheme_name 
      from apex_application_authorization auth
      where auth.application_id = pi_app_id1
       minus
      select  auth.authorization_scheme_name 
      from apex_application_authorization auth
      where auth.application_id = pi_app_id2
     ) t;
 end  app_appl_authorization_not_in; 

 procedure app_page_report_ir_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number; 
 v_title       varchar2(60)  := ac_utils.G_AUTHORIZATION ;
 v_msg         varchar2(1000);
 begin
   v_msg       := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_AUTHORIZATION );
    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.region_name ,'N', 'N' from
    (select ir.region_name
       from apex_application_page_ir ir
      where ir.application_id = pi_app_id1
        and ir.page_id = pi_page_id
      minus
     select ir.region_name 
       from apex_application_page_ir ir
      where ir.application_id = pi_app_id2
        and ir.page_id = pi_page_id
     ) t;
 end  app_page_report_ir_not_in; 

 procedure app_report_ir_col_not_in(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is 
 v_parent_id   number; 
 v_title       varchar2(60) := ac_utils.G_INTERACTIVE_REPORT_COLS ;
 v_msg         varchar2(1000);
 begin
   v_msg  := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_INTERACTIVE_REPORT_COLS );

    insert into ac_results
    (result_id,            parent_id, param_name,                  is_branch, is_src)
     select p_result_id, v_parent_id, t.column_alias ,'N', 'N' from
    (select ir.column_alias
       from apex_application_page_ir_col ir
      where ir.application_id = pi_app_id1
        and ir.page_id = pi_page_id
      minus
     select ir.column_alias 
       from apex_application_page_ir_col ir
      where ir.application_id = pi_app_id2
        and ir.page_id = pi_page_id
     ) t;

 end  app_report_ir_col_not_in;

 procedure app_page_process(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, pi_page_id number) is
 v_parent_id   number; 
 v_title       varchar2(60) := ac_utils.G_PAGE_PROCESS;
 v_msg         varchar2(1000);
  begin
   v_msg  := ac_utils.in1NotIn2(v_title, pi_app_id1, pi_app_id2);
   v_parent_id := ac_utils.ins_result_msg(p_result_id, pi_parent_id, v_msg, 1, true, ac_utils.G_PAGE_PROCESS );
   insert into ac_results
    (result_id,            parent_id, 
     param_name,           is_branch, is_src)
     select p_result_id, v_parent_id, 
     t.process_point || ' seq:'|| t.execution_sequence ,'N', 'N' from
    (select pr.process_point, pr.execution_sequence 
      from apex_application_page_proc pr
      where pr.application_id = pi_app_id1
        and pr.page_id = pi_page_id
      minus
      select pr.process_point, pr.execution_sequence
      from apex_application_page_proc pr
      where pr.application_id = pi_app_id2
        and pr.page_id = pi_page_id
    ) t;
  end;   


end ac_diff_not_in;
/
